package cn.hf.mychat.controller;

import cn.hf.mychat.config.Result;
import cn.hf.mychat.constanst.FinancialConst;
import cn.hf.mychat.constanst.StringUtils;
import cn.hf.mychat.entity.po.TbSysUser;
import cn.hf.mychat.entity.test.ExcelUtil;
import cn.hf.mychat.service.UserService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

@Slf4j
@RestController
@RequestMapping("/hf")
@RequiredArgsConstructor
public class DeriveData {

    private final UserService userService;

    /**
     * 导出表格
     * @param request
     * @param response
     */
    @GetMapping("/reportMail/reportExcel")
    public void queryReportExcel( HttpServletRequest request, HttpServletResponse response){

        Object[][] content = new Object[0][0];
      /*  //是否导出报告邮寄信息
        if (StringUtils.isNull(orderExtendReportRequestEntity.getNullExcel())) {
            List<OrderExtendReportResponseEntity> list = orderService.queryReportExcel(orderExtendReportRequestEntity);
            content = orderService.reportExcel(list);
        }*/
        List<TbSysUser> list = userService.findAll();
        content = DeriveData.reportExcel(list);
        // 创建HSSFWorkbook
        XSSFWorkbook wb = ExcelUtil.getXSSFWorkbook(FinancialConst.TEST_NAME,
                FinancialConst.TEST, content, null);
        // 响应到客户端
        try {
            // excel文件名
            String fileName = "表名" + System.currentTimeMillis() + ".xlsx";
            ExcelUtil.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            log.error("表格导出失败", e);
            throw new BadRequestAlertException("表格导出失败", "queryReportExcel", "");
        }
    }



    @PostMapping("/uploadUpdate")
    public Result uploadUpdate(
            @RequestParam("file") MultipartFile uploadFile, HttpServletRequest request) {
        String columns[] = {"用户ID","用户名","密码","电话号码","备注","创建用户","创建时间","更新用户","更新时间","1已删除 0未删除"};
        List<Map<String, String>> list = readExcel(uploadFile, columns);
        List<TbSysUser> tbSysUsers = new ArrayList<>();
        //遍历解析出来的list
        for (Map<String, String> map : list) {
            TbSysUser tbSysUser = new TbSysUser();
            tbSysUser.setUserId(Long.parseLong(map.get("用户ID")));
            tbSysUser.setUserName(map.get("用户名"));
            tbSysUser.setPhone(map.get("电话号码"));
            tbSysUser.setPassword("密码");
            tbSysUsers.add(tbSysUser);
        }

       int count = this.userService.updateAll(tbSysUsers);
        return Result.success(count);
    }


    public static Object[][] reportExcel(List<TbSysUser> list) {
        Object[][] content = new Object[list.size()][FinancialConst.TEST.length];
        for (int i = 0; i < list.size(); i++) {
            TbSysUser report = list.get(i);
            content[i][0] = report.getUserId();
            content[i][1] = report.getUserName();
            content[i][2] = report.getPassword();
            content[i][3] = report.getPhone();
            content[i][4] = report.getRemark();
            content[i][5] = report.getCreateUser();
            content[i][6] = report.getCreateTime();
            content[i][7] = report.getUpdateUser();
            content[i][8] = report.getUpdateTime();
            content[i][9] = report.getDeleted();
        }
        return content;
    }
    //读取excel
    static List<Map<String, String>> readExcel(MultipartFile uploadFile, String columns[]) {
        Workbook wb = null;
        List<Map<String, String>> list = new ArrayList<>();
        Sheet sheet = null;
        Row row = null;
        String cellData = null;
        /**读取excel文件*/
        String filePath = uploadFile.getOriginalFilename();
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        try {
            InputStream is = uploadFile.getInputStream();
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        /**处理文件数据*/
        if (wb != null) {
            //用来存放表中数据
            list = new ArrayList<Map<String, String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i < rownum; i++) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                row = sheet.getRow(i);//第几行
                if (row != null) {
                    for (int j = 0; j < colnum; j++) {
                        //第几列
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                } else {
                    break;
                }
                list.add(map);
            }
        }
        return list;
    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

}
